/*
 * PrintOrderDAO.java
 *
 * Created on March 22, 2007, 1:45 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.group1E.data;

import edu.byu.isys413.group1E.handlers.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * Handles the DB interaction for PrintOrders
 * @author tkulbeth
 */
public class PrintOrderDAO extends RevenueSourceDAO{
    /**
     * contains an instance of PrintOrderDAO
     */
    private static PrintOrderDAO instance = null;
    
    /** Creates a new instance of PrintOrderDAO */
    public PrintOrderDAO() {
    }
    
    /**
     * Retrieves an instance of the TransactionDAO object*
     * @return instance
     */
    public static synchronized PrintOrderDAO getInstance() {
        if (instance == null) {
            instance = new PrintOrderDAO();
        }
        return instance;
    }
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   CREATE
    /**
     *     * @throws java.lang.Exception Handles other exceptions
     * @return Returns the appropriate object or string
     * @throws java.lang.Exception to catch any errors in the process
     */
    public RevenueSourceBO create() throws Exception{
        String id = GUID.generate();
        PrintOrderBO bo = new PrintOrderBO(id);
        bo.setType("sale");
        Cache c = Cache.getInstance();
        c.put(bo.getRevSrcID(), bo);
        return bo;
    }
    
    /**
     * Reads in a PrintOrderBO by id
     * @param id contains the id
     * @return Returns the appropriate object or string
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public RevenueSourceBO read(String id) throws SQLException, DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        
        RevenueSourceBO bo = (PrintOrderBO)c.get(id);
        if (bo != null){
            return bo;                                                        // if so, return it immediately
        }
        
        //otherwise, create a new bo object with the correct key, save it to the cache, and call super.read to fill in its data
        //otherwise, get it from the database
        try {
            Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
            bo = (PrintOrderBO)this.read(id, conn);                                              // call read with a connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            //rollback
            throw new DataException("Could not retrieve record for id = " + id, e);
        }catch (ConnectionPoolException x) {
            //rollback
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return bo;
    }
    
    /**Reads in a PrintOrderBO by id using a connection
     * 
     * @param id contains the id
     * @param conn contains an existing Connection
      
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     * @return Returns the appropriate object or string*/
    public synchronized RevenueSourceBO read(String id, Connection conn) throws SQLException, DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        PrintOrderBO bo = (PrintOrderBO)c.get(id);
        if (bo != null){
            return bo;                                                        // if so, return it immediately
        }
        //put it to the cache so the super.read can get it
        bo = new PrintOrderBO(id);        
        c.put(id, bo);
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        
        bo = (PrintOrderBO)super.read(id, conn);
        
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM PRINT_ORDER_LINE WHERE RevenueSourceID = '" + id + "'");
        conn.commit();
        
        //build the object from the result set
        if (rs.next()) {
            
                bo.setRevSrcID              (id);
                bo.setPhotoSetID          (rs.getString("PHOTOSETID"));
                bo.setPrintFormatID     (rs.getString("PRINTFORMATID"));
                bo.setQty                       (rs.getInt("QTY"));
                bo.setStatus                    (rs.getString("STATUS"));
            
            bo.setAlreadyInDB(true);
            bo.setIsDirty(false);
            
            // save to the cache
            c.put(bo.getRevSrcID(), bo);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the BO
        return bo;
    }
    
    /**
     * reads all PrintOrderBOs
     * @return Returns the appropriate object or string
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException catches any errors with the connection
     */
    public synchronized ArrayList<RevenueSourceBO> readAll() throws DataException, SQLException, ConnectionPoolException{
//ProductDAO prodDAO = ProductDAO.getInstance();
        ArrayList<RevenueSourceBO> list = new ArrayList<RevenueSourceBO>();
//now build the query
        String query = "SELECT * FROM PRINT_ORDER_LINE";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
//extract the elements from the result set and build a list of results
//build the object from the result set
        while (rs.next()) {
            PrintOrderBO bo = new PrintOrderBO(rs.getString("RevenueSourceID"));
            Cache.getInstance().put(rs.getString("RevenueSourceID"), bo);
            bo = (PrintOrderBO)super.read(rs.getString("RevenueSourceID"), conn);

                bo.setRevSrcID              (rs.getString("RevenueSourceID"));
                bo.setPhotoSetID          (rs.getString("PHOTOSETID"));
                bo.setPrintFormatID     (rs.getString("PRINTFORMATID"));
                bo.setQty                       (rs.getInt("QTY"));
                bo.setStatus                    (rs.getString("STATUS"));
                
            bo.setAlreadyInDB(true);
            bo.setIsDirty(false);
            
// save to the list
            list.add(bo);
        }//while
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return list;
    }
    
    /**
     * Updates an existing record in the table
     * 
     * 
     * @param bo contains a productBO to be updated to the table
     * @param conn contains an existing Connection
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     */
    protected void update(RevenueSourceBO bo, Connection conn) throws SQLException, DataException {
        //update the DB
        PrintOrderBO BO = (PrintOrderBO)bo;
        PreparedStatement pstmt = conn.prepareStatement("UPDATE PRINT_ORDER_LINE SET PHOTOSETID = ?,  PRINTFORMATID = ?,  QTY = ?,  STATUS = ?  WHERE revenueSourceID = ?");
        

                pstmt.setString(1, BO.getPhotoSetID());
                pstmt.setString(2, BO.getPrintFormatID());
                pstmt.setInt(3, BO.getQty());
                pstmt.setString(4, BO.getStatus());
                pstmt.setString(5, BO.getRevSrcID());
        
        pstmt.executeUpdate();
        pstmt.close();
        BO.setAlreadyInDB(true);
    }// end update
    /**
     * Inserts a new record into the database
     * 
     * 
     * @param bo contains the object to be saved
     * @param conn contains the existing Connection
     * @throws edu.byu.isys413.group1E.handlers.DataException handles any errors from data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     */    
    synchronized void insert(RevenueSourceBO bo, Connection conn) throws SQLException, DataException {
//update the DB
        PrintOrderBO BO = (PrintOrderBO)bo;
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO PRINT_ORDER_LINE (revenueSourceID,  PHOTOSETID ,  PRINTFORMATID, QTY, STATUS ) VALUES ( ?, ?, ?, ?, ?)");
             
                pstmt.setString(1, BO.getRevSrcID());
                pstmt.setString(2, BO.getPhotoSetID());
                pstmt.setString(3, BO.getPrintFormatID());
                pstmt.setInt(4, BO.getQty());
                pstmt.setString(5, BO.getStatus());
        
        
        pstmt.executeUpdate();
        pstmt.close();
// tell the object that it's now in the db (so we call update next time not insert)
        BO.setAlreadyInDB(true);
    }//end insert
    
    
    
      
    /**
     * Returns an array list of PrintOrders
     * @return Returns the appropriate object or string
     * @throws java.sql.SQLException handles sql exceptions
     * @throws edu.byu.isys413.group1E.handlers.DataException Handles Data Exceptions
     * @throws edu.byu.isys413.group1E.handlers.ConnectionPoolException handles connection problems
     */
    public synchronized ArrayList<RevenueSourceBO> getReady() throws DataException, SQLException, ConnectionPoolException{
//ProductDAO prodDAO = ProductDAO.getInstance();
        ArrayList<RevenueSourceBO> list = new ArrayList<RevenueSourceBO>();
//now build the query
        String query = "SELECT * FROM PRINT_ORDER_LINE where status = 'Ready' ";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
//extract the elements from the result set and build a list of results
//build the object from the result set
        while (rs.next()) {
            PrintOrderBO bo = new PrintOrderBO(rs.getString("RevenueSourceID"));
            Cache.getInstance().put(rs.getString("RevenueSourceID"), bo);
            bo = (PrintOrderBO)super.read(rs.getString("RevenueSourceID"), conn);

                bo.setRevSrcID              (rs.getString("RevenueSourceID"));
                bo.setPhotoSetID          (rs.getString("PHOTOSETID"));
                bo.setPrintFormatID     (rs.getString("PRINTFORMATID"));
                bo.setQty                       (rs.getInt("QTY"));
                bo.setStatus                    (rs.getString("STATUS"));
                
            bo.setAlreadyInDB(true);
            bo.setIsDirty(false);
            
// save to the list
            list.add(bo);
        }//while
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return list;
    }
    
    
    
}
